/**
 * Created by XiaAo on 2017/4/31.
 * function :本地数据库module
 * author ：xiaao
 * data : 2017-4-31
 */

var webSQL = {

    //student：获取或者创建一个数据库，如果数据库不存在那么创建之
    //var dataBase = openDatabase("", "1.0", "学生表", 1024 * 1024, function () { });
    /**
     * 打开数据库,  或者直接连接数据库参数：数据库名称，版本，概述，大小
     * 如果数据库不存在那么创建之
     * @returns  dataBase:打开成功   null:打开失败("myDb", "1.0", "it's to save student data!", 1024 * 1024)
     */
    websqlOpenDB: function () {
        /*数据库有就打开 没有就创建*/
        dataBase = window.openDatabase("myDb", "1.0", "it's to save student data!", 1024 * 1024, function () {
        });
        if (dataBase) {
            console.log("数据库创建/打开成功!");
        } else {
            console.log("数据库创建/打开失败！");
        }
        return dataBase;
    },

    /**
     * 新建数据库里面的表单
     * @param tableName:表单名
     */
    websqlCreatTable: function (tableName) {
        var creatTableSQL = "CREATE TABLE IF NOT EXISTS" + tableName + "(sID SMALLINT  NULL, sNAME TEXT NULL,sSEX TEXT NULL," +
            "sPAPER TEXT NULL,sPATENT TEXT NULL,sRESEARCH TEXT NULL,sREWARD TEXT NULL,sSESSION TEXT NULL";
        dataBase.transaction(function (ctx, result) {
            ctx.executeSql(creatTableSQL, [], function (ctx, result) {
                console.log("表创建成功 " + tableName);
            }, function (tx, error) {
                console.log('创建表失败:' + tableName + error.message);
            });
        });
    },

    /**
     * 往表单里面插入数据
     * @param tableName:表单名
     * @param NAME:姓名
     * @param AGE:年龄
     * @param HEIGHT:身高
     * @param WEIGTH:体重
     */
    websqlInsterDataToTable: function (tableName, sId, sName, sSex, sPaper, sPatent, sResearch, sReward, sSession) {
        var insterTableSQL = 'INSERT INTO ' + tableName + ' (sID, sNAME, sSEX, sPAPER, sPATENT, sRESEARCH, sREWARD, sSESSION) VALUES (?,?,?,?,?,?,?,?)';
        dataBase.transaction(function (ctx) {
            ctx.executeSql(insterTableSQL, [sId, sName, sSex, sPaper, sPatent, sResearch, sReward, sSession],
                function (ctx, result) {
                    console.log("插入" + tableName + sName + "成功");
                },
                function (tx, error) {
                    console.log('插入失败: ' + error.message);
                });
        });
    },

    /**
     * 获取数据库一个表单里面的所有数据
     * @param tableName:表单名
     * 返回数据集合
     */
    websqlGetAllData: function (tableName) {
        var selectALLSQL = 'SELECT * FROM ' + tableName;
        dataBase.transaction(function (ctx) {
            ctx.executeSql(selectALLSQL, [], function (ctx, result) {
                    console.log('查询成功: ' + tableName + result.rows.length);
                    var len = result.rows.length;
                    for (var i = 0; i < len; i++) {
                        console.log("sName = " + result.rows.item(i).sNAME);
                        // console.log("AGE = "  + result.rows.item(i).AGE);
                        // console.log("HEIGHT = "  + result.rows.item(i).HEIGHT);
                        // console.log("WEIGTH = "  + result.rows.item(i).WEIGTH);
                        console.log("-------- 我是分割线 -------");
                    }
                },
                function (tx, error) {
                    console.log('查询失败: ' + error.message);
                });
        });
    },

    /**
     * 获取数据库一个表单里面的部分数据
     * @param tableName:表单名
     * @param name:姓名
     */
    websqlGetAData: function (tableName, name) {
        var selectSQL = 'SELECT * FROM ' + tableName + ' WHERE sNAME = ?';
        dataBase.transaction(function (ctx) {
            ctx.executeSql(selectSQL, [name], function (ctx, result) {
                    console.log('查询成功: ' + tableName + result.rows.length);
                    var len = result.rows.length;
                    for (var i = 0; i < len; i++) {
                        console.log("sName = " + result.rows.item(i).sNAME);
                        // console.log("AGE = "  + result.rows.item(i).AGE);
                        // console.log("HEIGHT = "  + result.rows.item(i).HEIGHT);
                        // console.log("WEIGTH = "  + result.rows.item(i).WEIGTH);
                    }
                },
                function (tx, error) {
                    console.log('查询失败: ' + error.message);
                });
        });
    },

    /**
     * 删除表单里的全部数据
     * @param tableName:表单名
     */
    websqlDeleteAllDataFromTable: function (tableName) {
        var deleteTableSQL = 'DELETE FROM ' + tableName;
        localStorage.removeItem(tableName);
        dataBase.transaction(function (ctx, result) {
            ctx.executeSql(deleteTableSQL, [], function (ctx, result) {
                console.log("删除表成功 " + tableName);
            }, function (tx, error) {
                console.log('删除表失败:' + tableName + error.message);
            });
        });
    },

    /**
     * 根据name删除数据
     * @param tableName:表单名
     * @param name:数据的姓名
     */
    websqlDeleteADataFromTable: function (tableName, name) {
        var deleteDataSQL = 'DELETE FROM ' + tableName + ' WHERE sNAME = ?';
        localStorage.removeItem(tableName);
        dataBase.transaction(function (ctx, result) {
            ctx.executeSql(deleteDataSQL, [name], function (ctx, result) {
                alert("删除成功 " + tableName + name);
            }, function (tx, error) {
                alert('删除失败:' + tableName + name + error.message);
            });
        });
    },

    /**
     * 根据name修改数据
     * @param tableName:表单名
     * @param name:姓名
     * @param age:年龄
     */
    websqlUpdateADat: function a(tableName, name, age) {
        var updateDataSQL = 'UPDATE ' + tableName + ' SET AGE = ? WHERE NAME = ?';
        dataBase.transaction(function (ctx, result) {
            ctx.executeSql(updateDataSQL, [age, name], function (ctx, result) {
                alert("更新成功 " + tableName + name);
            }, function (tx, error) {
                alert('更新失败:' + tableName + name + error.message);
            });
        });
    },

    websqlInit: function (tablename) {
        websqlOpenDB();
        websqlCreatTable(tablename);
        alert(tablename + '创建成功');
    }

}

module.exports = webSQL;




